import logging
from mysql.connector import pooling
from dotenv import load_dotenv
import os
from datetime import datetime

# 加载.env文件
load_dotenv()

host = os.getenv('MYSQL_HOST')
user = os.getenv('MYSQL_USER')
password = os.getenv('MYSQL_PASSWORD')
db = os.getenv('MYSQL_DB')

# 创建一个连接池
dbconfig = {
    "host": host,
    "user": user,
    "password": password,
    "database": db
}
pool = pooling.MySQLConnectionPool(pool_name="my_pool", pool_size=10, **dbconfig)

logging.basicConfig(filename='../logs/keywords.log',
                    level=logging.DEBUG,
                    format='%(asctime)s - %(levelname)s - %(message)s')

conn = pool.get_connection()
cursor = conn.cursor()


class KeyWords:
    def __init__(self, keyword, create_time, update_time):
        self.Keyword = keyword
        self.CreateTime = create_time
        self.UpdateTime = update_time

    # 获取不是当前这个小时的关键词
    @staticmethod
    def getList():
        sql = "SELECT * FROM keywords WHERE HOUR(update_time) <> HOUR(%s)"
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        cursor.execute(sql, (current_time,))
        result = cursor.fetchall()
        return result

    def getListByKeyword(self):
        keyword = self.keyword()
        sql = "SELECT * FROM keywords WHERE keyword = %s"
        cursor.execute(sql, (keyword,))
        result = cursor.fetchall()
        return result

    def edit(self):
        keyword = self.keyword
        sql = "UPDATE keywords SET keyword = %s, update_time = %s WHERE keyword = %s"
        cursor.execute(sql, (keyword, self.CreateTime, self.UpdateTime))
        conn.commit()
        return True

    def add(self):
        keyword = self.Keyword
        sql = "INSERT INTO keywords (keyword, create_time, update_time) VALUES (%s, %s, %s)"
        cursor.execute(sql, (keyword, self.CreateTime, self.UpdateTime))
        conn.commit()
        return True

    def delete(self):
        keyword = self.Keyword
        sql = "DELETE FROM keywords WHERE keyword = %s"
        cursor.execute(sql, (keyword,))
        conn.commit()
        return True

    def updateTime(self):
        keyword = self.Keyword
        sql = "UPDATE keywords SET update_time = %s WHERE keyword = %s"
        cursor.execute(sql, (self.UpdateTime, keyword))
        conn.commit()
        return True
